package cn.com.fattiger.framework.cms.admin.common.service.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import cn.com.fattiger.framework.cms.admin.common.service.DynamicQueryService;
import cn.com.fattiger.framework.cms.admin.common.service.TemplateService;
import freemarker.cache.StringTemplateLoader;
import freemarker.cache.TemplateLoader;
import freemarker.template.Configuration;

/**
 * Created by Administrator on 2016/3/7.
 */
@Service
public class DynamicQueryServiceImpl implements DynamicQueryService {

	@Autowired
	JdbcTemplate jdbcTemplate;

	@Autowired
	TemplateService templateService;

	@Autowired
	private Configuration templateConfiguration;

	@Override
	public Map<String, Object> query(Integer queryId, Map<String, Object> data)
			throws Exception {
		Assert.notNull(queryId, "queryId must not be null");
		Assert.notNull(data, "data must not be null");

		Map<String, Object> queryResult = new HashMap<>();
		queryResult.putAll(data);

		// default page and pageSize.
		Assert.notNull(data.get("page"), "page must not be null");
		Assert.notNull(data.get("pageSize"), "pageSize must not be null");

		String _sql_ = "select * from tb_dynamic_query_command where id = "
				+ queryId;
		Map<String, Object> cmd = jdbcTemplate.queryForMap(_sql_);
		Assert.notNull(cmd, "no command found for " + queryId);

		// query result.
		String sql_list = templateService.templateToString("query-template-"
				+ cmd.get("id"), data);
		Assert.notNull(sql_list, "SQL must not be null");
		List<Map<String, Object>> _dat = jdbcTemplate.queryForList(sql_list);
		queryResult.put("queryResult", _dat);
		// query result count.
		String sql_cnt = templateService.templateToString("query-template-"
				+ cmd.get("id") + "-cnt", data);
		Assert.notNull(sql_cnt, "SQL must not be null");
		Integer cnt = jdbcTemplate.queryForObject(sql_cnt, Integer.class);
		queryResult.put("total", cnt);
		int pageSize = (int)data.get("pageSize");
		int totalPage = cnt/pageSize +1;
		queryResult.put("totalPage", totalPage);
		return queryResult;
	}

	@Override
	public String fetchSql(Integer queryId, Map<String, Object> data)
			throws Exception {
		Assert.notNull(queryId, "queryId must not be null");
		Assert.notNull(data, "data must not be null");

		String _sql_ = "select * from tb_dynamic_query_command where id = "
				+ queryId;
		Map<String, Object> cmd = jdbcTemplate.queryForMap(_sql_);
		Assert.notNull(cmd, "no command found for " + queryId);

		// query result.
		String sql = templateService.templateToString(
				"query-template-" + cmd.get("id"), data);
		Assert.notNull(sql, "SQL must not be null");
		return sql;
	}

	@Override
	public Integer insert(Integer queryId, final Map<String, Object> data)
			throws Exception {

		Assert.notNull(queryId, "queryId must not be null");
		Assert.notNull(data, "data is not be null.");

		String keys = StringUtils.join(data.keySet(), ',');
		String[] _values_ = new String[data.size()];
		Arrays.fill(_values_, "?");
		String values = StringUtils.join(_values_, ',');

		Map<String, Object> params = new HashMap<>();
		params.put("keys", keys);
		params.put("values", values);

		final String sql = fetchSql(queryId, params);
		System.out.println("sql:" + sql);

		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
				int i = 1;
				for (String key : data.keySet()) {
					ps.setObject(i, data.get(key));
					i++;
				}
				return ps;
			}
		}, keyHolder);
		Integer id = keyHolder.getKey().intValue();
		return id;
	}

	@Override
	public void refreshTemplate(Integer queryId) throws Exception {
		String sql = "select * from tb_dynamic_query_command ";
		if (null != queryId) {
			sql = sql + "where id = " + queryId;
		}

		List<Map<String, Object>> cmds = jdbcTemplate.queryForList(sql);
		Assert.notNull(cmds, "no query template found.");

		StringTemplateLoader stringTemplateLoader;
		TemplateLoader templateLoader = templateConfiguration
				.getTemplateLoader();
		if (null != templateLoader
				&& templateLoader instanceof StringTemplateLoader) {
			stringTemplateLoader = (StringTemplateLoader) templateLoader;
		} else {
			stringTemplateLoader = new StringTemplateLoader();
		}
		for (Map<String, Object> cmd : cmds) {
			if (null != cmd.get("query").toString()) {
				stringTemplateLoader.putTemplate(
						"query-template-" + cmd.get("id"), cmd.get("query")
								.toString());
			}

			if (null != cmd.get("queryCount")) {
				stringTemplateLoader.putTemplate(
						"query-template-" + cmd.get("id") + "-cnt",
						cmd.get("queryCount").toString());
			}
		}
		templateConfiguration.setTemplateLoader(stringTemplateLoader);
	}

	@PostConstruct
	private void initLoadTemplate() {
		try {
			refreshTemplate(null);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
